Here’s an example of a stored procedure with two parameters @Beginning_Date and

@Ending_Date. You know they are parameters because of the @ in front of the variable name. This one comes from the Northwind database

How to execute a stored procedure with an input parameter (Enterprise Manager)

To execute a stored procedure with an input parameter using the Execute SQL task

1. In the Execute SQL Task Properties dialog box, in the SQL statement box, type the parameterized SQL stored procedure statement. For example:

2. exec byRoyalty ?

3. Click Parameters, click the Input Parameters tab, and then assign a global variable and its value to the parameter.

©1988-2000 Microsoft Corporation. All Rights Reserved.

Go to the SQL Query Analyzer and type the command shown.

In this example it doesn’t work because there is a parameter missing as the message indicates.

A correct command would be:

execute Salesbycategory @category=’...’ where the ... is replaced by a valid category name from the table.

The TryNorthwind.adp is an Access Project which is linked to the Northwind database. There are two types of icons shown, those which indicate and execute queries and and those which indicate and accesss stored procedures

You execute the stored procedure by double clicking it. IF there is a parameter required, a window comes up and prompts you. IF no parameter is needed the stored procedure returns the values requested.

To execute a stored procedure (or a view) in SQL Server, open the query analyzer and type the execute command. If the name of the stored procedure has blanks in it, you need double quotes around it. If you need parameters, they need to be separated by commas and the values need single quotes around them if they are not numeric. Here’s an example.

execute "Sales by Year" @Beginning_Date='1/12/1996', @Ending_Date='2/1/1997'